<?php
// collection of all functions which will be called in base.*.php or it's childs
interface createTable
{
    public function create_ASDFSDGK8792SD32();
}


// All function name will come through an interface so child classes can call them by name
class sqlService implements createTable{

	private $sqlAdapter = null;
	
	// general information like name/desc for all groups
	public function select_VMLOYTKSDVFLGDLGLD()
	{
		global $wpdb;
		$sql = "SELECT * FROM wpfb_group_information order by name asc;";
		$databaseRows = $wpdb->get_results($sql,ARRAY_A);
		if( $wpdb->last_error)
		{
			throw new Exception($wpdb->last_error);
		}
		return $databaseRows;
	}

	// group information for only those groups whose data are imported; Usually for a dropdown button
	public function select_ACVWBWPERIDFGLD76KS()
	{
		global $wpdb;
		$sql = "SELECT A.* FROM wpfb_group_information A where A.group_id in (select distinct B.from_group_id from wpfb_group_post_collection B) order by name asc;";
		$databaseRows = $wpdb->get_results($sql,ARRAY_A);
		if( $wpdb->last_error)
		{
			throw new Exception($wpdb->last_error);
		}
		return $databaseRows;
	}
	
	// post collection information dashboard
	public function select_BCMVBWRKDS5884XLDF()
	{
		global $wpdb;
		$sql = "select A.name, A.group_id, count(B.message) 'Total post', max(B.like_count) 'Max like', max(B.comment_count) 'Max comment #', min(B.message_creation_time) 'From', max(B.message_creation_time) 'To'";
		$sql .= " from wpfb_group_information A inner join wpfb_group_post_collection B";
		$sql .= " on A.group_id = B.from_group_id";
		$sql .= " group by A.group_id";
		$databaseRows = $wpdb->get_results($sql,ARRAY_A);
		if( $wpdb->last_error)
		{
			throw new Exception($wpdb->last_error);
		}
		return $databaseRows;
	}
	
	// view entries as specified by a weekno
	public function select_XCNVWKERSDLMC485DK($group_id, $valueOfWhich, $which)
	{
		global $wpdb;
		
		$table_name = 'wpfb_group_post_collection';
		$sql = "";
		
		if($which == 'weekNo')
		{
			$datetime = new DateTime();
			$datetime->setISODate((int)$datetime->format('Y'), $valueOfWhich, 1);
			$interval = new DateInterval('P1D');
			$week = new DatePeriod($datetime, $interval, 6);
			$dayList = array();
			foreach($week as $day){
				$dayList[] = $day->format('Y-m-d g:i:s');
			}
			$sql = sprintf("select * from %s A where A.from_group_id= '%s' and A.message_creation_time between '{$dayList[0]}' and '{$dayList[6]}'", $table_name, $group_id);			
		}
		else if($which == 'author')
		{
			$sql = sprintf("select * from %s A where A.from_group_id= '%s' and A.from_profile_id = '%s'", $table_name, $group_id, $valueOfWhich);			
		}

		$databaseRows = $wpdb->get_results($sql,ARRAY_A);
		if( $wpdb->last_error)
		{
			throw new Exception($wpdb->last_error);
		}
		return $databaseRows;
	}
	
	// view entries from database for defined user group and order by preference (like_count/creation time etc.) setby user.
	public function select_KCVPWEFCMV495KL($group_id,$sort_order)
	{
		global $wpdb;
		$table_name= 'wpfb_group_post_collection';
		$sql = sprintf( "select A.* from %s A group by A.message having A.from_group_id= '%s'", $table_name, $group_id);
		if( $sort_order != 'all')
			$sql .= sprintf(" order by A.%s desc limit 0,50;", $sort_order);
		//wp-prepare can be applied now.
		$databaseRows = $wpdb->get_results($sql,ARRAY_A);
		if( $wpdb->last_error)
		{
			throw new Exception($wpdb->last_error);
		}
		return $databaseRows;
	}
	
	// Posts of a group
	public function insert_BNCVUKERJSFQWUIR($responseData,$group_id)
	{
		global $wpdb;
		$i=0;
		foreach($responseData->data as $responseArrayRow)
		{
			// Code from: http://stackoverflow.com/a/1869147/385205
			$response = json_decode(json_encode($responseArrayRow), FALSE);
			
			$this->executeQuery('BEGIN;');
			++$i;
			$href= null;
			if( isset($response->id) )
			{
				$array= explode('_', $response->id);
				//$href= "https://www.facebook.com/groups/$group_id/{$array[1]}";
				// Found that: facebook.com/source-id/posts/child-id works for groups in which I'm a member + where I'm not a member.
				$href= "https://www.facebook.com/$group_id/posts/{$array[1]}";
			}
			else
			{
				$error = print_r($response, true);
				throw new Exception("Found a post without id:\n $error");
			}
			if( isset($response->link))
				$embededLink = $response->link;
			else
				$embededLink = "[None]";
			
			if( isset($response->picture))
			{
				$embededPicture = substr( $response->picture, 0, -6).'_n.jpg';
			}
			else
				$embededPicture = "[None]";
				
			$table_name = 'wpfb_group_post_collection';
			$wpdb->insert( 
				$table_name, 
				array( 
					'like_count' => $response->likes->summary->total_count,
					'from_name' => $response->from->name,
					'from_profile_id' => $response->from->id,
					'message' => strlen($response->message) == 0 ? "[No description]" : $response->message,
					'message_creation_time' => $response->created_time,
					'message_updated_time' => $response->updated_time,
					'message_permalink' => $href,
					'type_status_or_picture_etc' => $response->type,
					'resource_href_link' => $embededLink,
					'resource_picture_link' => $embededPicture,
					'comment_count' => $response->comments->summary->total_count,
					'from_group_id' => $group_id
				), 
				array( 
					'%d',
					'%s',
					'%s',
					'%s',
					'%s', // created_time
					'%s', // updated_time
					'%s',
					'%s',
					'%s',
					'%s',
					'%d', // comment count
					'%s'
				)
			);
			if( $wpdb->last_error)
			{
				$wpdb->update( 
					$table_name,  
					array( 
						'like_count' => $response->likes->summary->total_count,
						'comment_count' => $response->comments->summary->total_count,
						'message_updated_time' => $response->updated_time
					),
					array('message_permalink'=>$href)
				);
				if( $wpdb->last_error)
					throw new Exception($wpdb->last_error);
			}
		}
		return $i;
	}
	
	// group info insert
	public function insert_CBNVRYKGSG46GHF($response)
	{
		global $wpdb;
		$table_name = 'wpfb_group_information';
		$wpdb->insert( 
			$table_name, 
			array( 
				'name' => $response['name'],
				'description' => $response['description'],
				'group_id' => $response['id']
			), 
			array(
				'%s',
				'%s',
				'%s'
			)
		);
		if( $wpdb->last_error)
		{
			$wpdb->update( 
				$table_name,  
				array( 
					'name' => $response['name'],
					'description' => $response['description']
				),
				array('group_id'=>$response['id'])
			);
			if( $wpdb->last_error)
				throw new Exception($wpdb->last_error);
		}
	}
	
	// User's group information for multiple groups
	public function insert_ASDGKLBFUER348SL($responseData)
	{
		global $wpdb;
		$i = 0;
		foreach( $responseData['groups']['data'] as $response)
		{
			++$i;
			$wpdb->insert( 
				'wpfb_group_information', 
				array( 
					'name' => $response['name'],
					'description' => $response['description'],
					'group_id' => $response['id']
				), 
				array(
					'%s',
					'%s',
					'%s'
				)
			);
			if( $wpdb->last_error)
			{
				$wpdb->update( 
					'wpfb_group_information', 
					array( 
						'name' => $response['name'],
						'description' => $response['description']
					),
					array('group_id'=>$response['id'])					
				);
				if( $wpdb->last_error)
					throw new Exception($wpdb->last_error);
			}
		}
		return $i;
	}
	
	// truncate all table
	public function truncate_CBMBCNIOR513W2S()
	{
		global $wpdb;
		$table_name= 'wpfb_group_post_collection';
		$sql = "TRUNCATE `$table_name`;";
		$this->executeQuery($sql);
		
		$table_name= 'wpfb_group_information';
		$sql = "TRUNCATE `$table_name`;";
		$this->executeQuery($sql);
	}
	
	// create wpfb tables
	public function create_ASDFSDGK8792SD32()
	{
		$table_name= 'wpfb_group_post_collection';
		$sql = "CREATE TABLE IF NOT EXISTS `$table_name` (";
  		$sql .= "`id` int(20) NOT NULL AUTO_INCREMENT,";
		$sql .= "`from_group_id` varchar(100) NOT NULL,";
  		$sql .= "`like_count` int(10) NOT NULL,";
  		$sql .= "`from_name` varchar(1000) CHARACTER SET utf8,";
  		$sql .= "`from_profile_id` varchar(1000) CHARACTER SET utf8 NOT NULL,";
  		$sql .= "`message` varchar(10000) CHARACTER SET utf8,";
  		$sql .= "`message_creation_time` datetime NOT NULL,";
		$sql .= "`message_updated_time` datetime NOT NULL,";
  		$sql .= "`message_permalink` varchar(200) CHARACTER SET utf8 NOT NULL,";
  		$sql .= "`type_status_or_picture_etc` varchar(100) CHARACTER SET utf8 NOT NULL,";
  		$sql .= "`resource_href_link` varchar(1000) CHARACTER SET utf8,";
		$sql .= "`resource_picture_link` varchar(1000) CHARACTER SET utf8,";
  		$sql .= "`comment_count` int(10) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY (`message_permalink`)";
		$sql .= ") AUTO_INCREMENT=1 collate utf8_general_ci";
		
		$this->executeQuery($sql);
			
		$table_name= 'wpfb_group_information';
		$sql = "CREATE TABLE IF NOT EXISTS `$table_name` (";
		$sql .= "`id` INT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY, `group_id` VARCHAR(255) CHARACTER SET utf8 UNIQUE, `name` VARCHAR(5000) CHARACTER SET utf8 NOT NULL, `description` VARCHAR(10000) CHARACTER SET utf8 NULL, UNIQUE KEY (`group_id`)) collate utf8_general_ci;";
		
		$this->executeQuery($sql);
		
		try{
			$this->executeQuery('drop index author_index on wpfb_group_post_collection;');
		}catch(Exception $e){}
		$sql = "CREATE INDEX AUTHOR_INDEX ON wpfb_group_post_collection (like_count, comment_count, message_creation_time)";
		$this->executeQuery($sql);
	}
	private function executeQuery($sql)
	{
		global $wpdb;
		$wpdb->query($sql);
		
		if( $wpdb->last_error)
		{
			throw new Exception($wpdb->last_error);
			return false;
		}
	}
	
	public function __construct() {
		//require_once('../../../wp-config.php');
		global $wpdb;
		$this->sqlAdapter = $wpdb;
	}
}
?>